package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Item;
import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.ItemDAO;

import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.sql.*;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.domain.Item;

public class ItemDAOImpl implements ItemDAO {

    private static final String getItemListByProductString = "SELECT I.ITEMID,LISTPRICE,"+
            "UNITCOST," +
            "SUPPLIER AS supplierId," +
            "I.PRODUCTID AS \"product.productId\"," +
            "NAME AS \"product.name\"," +
            "DESCN AS \"product.description\"," +
            "CATEGORY AS \"product.categoryId\"," +
            "STATUS," +
            "ATTR1 AS attribute1," +
            "ATTR2 AS attribute2," +
            "ATTR3 AS attribute3," +
            "ATTR4 AS attribute4," +
            "ATTR5 AS attribute5 " +
            "FROM ITEM I, PRODUCT P WHERE P.PRODUCTID = I.PRODUCTID AND I.PRODUCTID = ?";

    private static final String getItemString = "select I.ITEMID," +
            "LISTPRICE," +
            "UNITCOST," +
            "SUPPLIER AS supplierId," +
            "I.PRODUCTID AS \"product.productId\"," +
            "NAME AS \"product.name\"," +
            "DESCN AS \"product.description\"," +
            "CATEGORY AS \"product.categoryId\"," +
            "STATUS," +
            "ATTR1 AS attribute1," +
            "ATTR2 AS attribute2," +
            "ATTR3 AS attribute3," +
            "ATTR4 AS attribute4," +
            "ATTR5 AS attribute5," +
            "QTY AS quantity " +
            "from ITEM I, INVENTORY V, PRODUCT P where P.PRODUCTID = I.PRODUCTID and I.ITEMID = V.ITEMID and I.ITEMID = ? ";

    private static final String getItemString1 = "SELECT I.ITEMID,\n" +
            "            LISTPRICE,\n" +
            "            UNITCOST,\n" +
            "            SUPPLIER AS supplierId,\n" +
            "            I.PRODUCTID AS 'product.productId',\n" +
            "            NAME AS 'product.name',\n" +
            "            DESCN AS 'product.description',\n" +
            "            CATEGORY AS 'product.categoryId',\n" +
            "            STATUS, \n" +
            "            ATTR1 AS attribute1,\n" +
            "            ATTR2 AS attribute2,\n" +
            "            ATTR3 AS attribute3,\n" +
            "            ATTR4 AS attribute4,\n" +
            "            ATTR5 AS attribute5,\n" +
            "            QTY AS quantity \n" +
            "            FROM ITEM I, INVENTORY V, PRODUCT P WHERE P.PRODUCTID = I.PRODUCTID AND I.ITEMID = V.ITEMID AND  P.PRODUCTID = ?";

    private static final String getInventoryQuantityString = "SELECT QTY AS value FROM INVENTORY WHERE ITEMID = ?";

    private static final String updateInventoryQuantityString = "UPDATE INVENTORY SET QTY = QTY - ? WHERE ITEMID = ?";
    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(updateInventoryQuantityString);
            String itemID = param.keySet().iterator().next();
            Integer increment = (Integer) param.get(itemID);
            pStatement.setInt(1, increment.intValue());
            pStatement.setString(1,itemID);
            pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public int getInventoryQuantity(String itemId) {
        int result = -1;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(getInventoryQuantityString);
            pStatement.setString(1,itemId);
            ResultSet resultSet = pStatement.executeQuery();
            if(resultSet.next()){
                result = resultSet.getInt(1);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List<Item> getItemListByProduct(String productId) {
        List<Item> itemList = new ArrayList<>();
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection
                    .prepareStatement(getItemListByProductString);
            pStatement.setString(1,productId);
            ResultSet resultSet = pStatement.executeQuery();
            while (resultSet.next()){
                Item item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                itemList.add(item);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return itemList;
    }

    @Override
    public Item getItem(String itemId) {
        Item item = null;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(getItemString);
            pStatement.setString(1,itemId);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()){
                item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return item;
    }

    @Override
    public Item getItem1(String productId) {
        Item item = null;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(getItemString1);
            pStatement.setObject(1,productId);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()){
                item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return item;
    }

    @Override
    public Product getProductById(String productId) {
        Product product = null;
        try {
            Connection conn = DBUtil.getConnection();
            PreparedStatement psmt = conn.prepareStatement("select * from product where productId = ?");
            psmt.setObject(1,productId);
            ResultSet rs = psmt.executeQuery();
            if(rs != null){
                rs.next();
                product = new Product();
                product.setDescription(rs.getString("descn"));
                product.setName(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return product;
    }
}
